﻿using System;
using System.Data;
using System.Data.SqlClient;
using CentroVentas.Clases.Utiles;

namespace CentroVentas.Clases.Funciones
{
    class Proveedores
    {

        DataBase bd = new DataBase();

        #region variables
            int _id; 
            string _nombre, _email,_giro,_contacto,_telefono,_dir1,_dir2;
            // No se utiliza.
            // DateTime _creado;
            // bool _estado;
        #endregion
        
        #region constructor 

        public Proveedores() { }

        public Proveedores(int id) 
        {
            _id = id;
        }

        public Proveedores (string rut)
        {
            try
            {
                if (!this.Existe(rut))
                {
                    throw new Exception("Proveedor no existe");
                }
                else
                {
                    _id = GetIdProveedor(rut);
                }                
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion 

       #region propiedades        

       public int Id
        {
            get { return _id; }
        }

       public string Nombre
       {
           get
           {
               try
               {
                   DataSet dset = new DataSet();
                   bd.dadap.SelectCommand = new SqlCommand("SELECT proveedor.nombre FROM proveedor WHERE proveedor.id = @id", bd.Conn);
                   bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.Fill(dset, "Nombre");
                   return dset.Tables["Nombre"].Rows[0].ItemArray[0].ToString();
               }
               catch (Exception)
               {
                   throw;
               }
           }
           set
           {
               try
               {
                   bd.dadap.UpdateCommand = new SqlCommand("UPDATE proveedor SET nombre = @value WHERE id = @id", bd.Conn);
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.VarChar)).Value = value;
                   bd.dadap.UpdateCommand.ExecuteNonQuery();
                   _nombre = value;
               }
               catch (Exception ex)
               {
                   throw new Exception(ex.Message);
               }               
           }
       }

       public string Giro
       {
           get
           {
               try
               {
                   DataSet dset = new DataSet();
                   bd.dadap.SelectCommand = new SqlCommand("SELECT proveedor.giro FROM proveedor WHERE proveedor.id = @id", bd.Conn);
                   bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.Fill(dset, "Giro");
                   return dset.Tables["Giro"].Rows[0].ItemArray[0].ToString();
               }
               catch (Exception)
               {
                   throw;
               }
           }
           set
           {
               try
               {
                   bd.dadap.UpdateCommand = new SqlCommand("UPDATE proveedor SET giro = @value WHERE id = @id", bd.Conn);
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.VarChar)).Value = value;
                   bd.dadap.UpdateCommand.ExecuteNonQuery();
                   _giro = value;
               }
               catch (Exception ex)
               {
                   throw new Exception(ex.Message);
               }
           }
       }

       public string Ciudad
       {
           get
           {
               try
               {
                   DataSet dsCiudad = new DataSet();
                   DataBase db = DataBase.GetInstance();
                   db.dadap.SelectCommand = new SqlCommand("SELECT comuna.nombre FROM comuna,proveedor WHERE comuna.id = proveedor.comuna AND proveedor.id = @id", db.Conn);
                   db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   db.dadap.Fill(dsCiudad, "comuna");
                   return dsCiudad.Tables["comuna"].Rows[0].ItemArray[0].ToString();
               }
               catch (Exception)
               {                        
                   throw;
               }
           }           
       }

       public string Rut 
       {
           get
           {
               try
               {
                   DataSet dset = new DataSet();
                   bd.dadap.SelectCommand = new SqlCommand("SELECT proveedor.rut FROM proveedor WHERE proveedor.id = @id", bd.Conn);
                   bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.Fill(dset, "Rut");
                   return dset.Tables["Rut"].Rows[0].ItemArray[0].ToString();
               }
               catch (Exception ex)
               {
                   throw new Exception(ex.Message);
               }
           }
       }

       public string Contacto
       {
           get
           {
               try
               {
                   DataSet dset = new DataSet();
                   bd.dadap.SelectCommand = new SqlCommand("SELECT proveedor.vendedor FROM proveedor WHERE proveedor.id = @id", bd.Conn);
                   bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.Fill(dset, "Vendedor");
                   return dset.Tables["Vendedor"].Rows[0].ItemArray[0].ToString();
               }
               catch (Exception)
               {
                   throw;
               }
           }
           set
           {
               try
               {
                   bd.dadap.UpdateCommand = new SqlCommand("UPDATE proveedor SET vendedor = @value WHERE id = @id", bd.Conn);
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.VarChar)).Value = value;
                   bd.dadap.UpdateCommand.ExecuteNonQuery();
                   _contacto = value;
               }
               catch (Exception ex)
               {
                   throw new Exception(ex.Message);
               }
           }
       }

       public string Telefono
       {
           get
           {
               try
               {
                   DataSet dset = new DataSet();
                   bd.dadap.SelectCommand = new SqlCommand("SELECT proveedor.telefono FROM proveedor WHERE proveedor.id = @id", bd.Conn);
                   bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar)).Value = _id;
                   bd.dadap.Fill(dset, "Telefono");
                   return dset.Tables["Telefono"].Rows[0].ItemArray[0].ToString();
               }
               catch (Exception)
               {
                   throw;
               }
           }
           set
           {
               try
               {
                   bd.dadap.UpdateCommand = new SqlCommand("UPDATE proveedor SET telefono = @value WHERE id = @id", bd.Conn);
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.Int)).Value = value;
                   bd.dadap.UpdateCommand.ExecuteNonQuery();
                   _telefono = value;
               }
               catch (Exception ex)
               {
                   throw new Exception(ex.Message);
               }
           }
       }

       public string Direccion1
       {
           get
           {
               try
               {
                   DataSet dset = new DataSet();
                   bd.dadap.SelectCommand = new SqlCommand("SELECT proveedor.direccion1 FROM proveedor WHERE proveedor.id = @id", bd.Conn);
                   bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.Fill(dset, "Direccion1");
                   return dset.Tables["Direccion1"].Rows[0].ItemArray[0].ToString();
               }
               catch (Exception)
               {
                   throw;
               }
           }
           set
           {
               try
               {
                   bd.dadap.UpdateCommand = new SqlCommand("UPDATE proveedor SET direccion1 = @value WHERE id = @id", bd.Conn);
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.VarChar)).Value = value;
                   bd.dadap.UpdateCommand.ExecuteNonQuery();
                   _dir1 = value;
               }
               catch (Exception ex)
               {
                   throw new Exception(ex.Message);
               }
           }
       }

       public string Direccion2
       {
           get
           {
               try
               {
                   DataSet dset = new DataSet();
                   bd.dadap.SelectCommand = new SqlCommand("SELECT proveedor.direccion2 FROM proveedor WHERE proveedor.id = @id", bd.Conn);
                   bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.Fill(dset, "Direccion2");
                   return dset.Tables["Direccion2"].Rows[0].ItemArray[0].ToString();
               }
               catch (Exception)
               {
                   throw;
               }
           }
           set
           {
               try
               {
                   bd.dadap.UpdateCommand = new SqlCommand("UPDATE proveedor SET direccion2 = @value WHERE id = @id", bd.Conn);
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.VarChar)).Value = value;
                   bd.dadap.UpdateCommand.ExecuteNonQuery();
                   _dir2 = value;
               }
               catch (Exception ex)
               {
                   throw new Exception(ex.Message);
               }
           }
       }
        
        public string Email
        {
           get
           {
               try
               {
                   DataSet dset = new DataSet();
                   bd.dadap.SelectCommand = new SqlCommand("SELECT proveedor.email FROM proveedor WHERE proveedor.id = @id", bd.Conn);
                   bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.Fill(dset, "email");
                   return dset.Tables["email"].Rows[0].ItemArray[0].ToString();
               }
               catch (Exception)
               {
                   throw;
               }
           }
           set
           {
               try
               {
                   bd.dadap.UpdateCommand = new SqlCommand("UPDATE proveedor SET email = @value WHERE id = @id", bd.Conn);
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id;
                   bd.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.VarChar)).Value = value;
                   bd.dadap.UpdateCommand.ExecuteNonQuery();
                   _email = value;
               }
               catch (Exception ex)
               {
                   throw new Exception(ex.Message);
               }
           }
       }
       #endregion 

       #region Metodos         
 
       private int GetIdProveedor(string rut)
       {
           try
           {
               DataSet dset = new DataSet();
               bd.dadap.SelectCommand = new SqlCommand("SELECT proveedor.id FROM proveedor WHERE proveedor.rut = @rut", bd.Conn);
               bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@rut", SqlDbType.Char)).Value = rut;
               bd.dadap.Fill(dset, "proveedor");
               return Convert.ToInt32(dset.Tables["proveedor"].Rows[0].ItemArray[0].ToString());
           }
           catch (Exception)
           {
               return 0;
               throw;
           }
       }

       public bool Existe(string RUT)
       {
           try
           {
               DataBase bd = new DataBase();
               DataSet ds = new DataSet();
               bd.dadap.SelectCommand = new SqlCommand("SELECT COUNT(id) FROM proveedor WHERE rut = @rut", bd.Conn);
               bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@rut", SqlDbType.VarChar)).Value = RUT.Replace(".", "");
               bd.dadap.Fill(ds, "ExisteRut");
               if (Convert.ToInt32(ds.Tables["ExisteRut"].Rows[0].ItemArray[0]) == 1) return true;
               return false;
           }
           catch (Exception)
           {
               Utilidades.mensaje(TipoMensaje.ERROR, "No se pudo obtener el dato.", 6);
               return false;
           }
       }
       #endregion
        
       #region Metodos estaticos

        public static int ProximoId()
       {           
            try
            {
                DataSet ds = new DataSet();
                DataBase bd = DataBase.GetInstance();

                bd.dadap.SelectCommand = new SqlCommand("SELECT MAX(id) AS 'Max' FROM PROVEEDOR",bd.Conn);
                bd.dadap.Fill(ds,"Max");
                return Convert.ToInt32(ds.Tables["Max"].Rows[0].ItemArray[0].ToString()) + 1;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);                  
            }
       }

       public static DataSet GetListaProveedores(bool VerSoloActivos, bool verEstado,string busqueda)
        {
            DataBase bd = new DataBase();
            DataSet ds = new DataSet();
            string consulta;
           consulta = "SELECT proveedor.id AS 'ID', proveedor.rut AS 'RUT',proveedor.nombre AS 'Nombre',proveedor.vendedor AS 'Contacto',proveedor.telefono AS 'Teléfono',proveedor.email AS 'Email'";
           if (verEstado)
               consulta += ",estado.descripcion AS 'Estado'";
           consulta += " FROM PROVEEDOR,estado WHERE estado.id = PROVEEDOR.estado AND (proveedor.rut LIKE '%" + busqueda + "%' OR proveedor.nombre LIKE '%" + busqueda + "%' OR proveedor.vendedor LIKE '%" + busqueda + "%')";
           
            if (!VerSoloActivos)
                consulta = consulta + " AND proveedor.estado = 1";

            bd.dadap.SelectCommand = new SqlCommand(consulta, bd.Conn);
            bd.dadap.Fill(ds, "proveedores");
            return ds;
        }

        public static bool ExisteProveedor(string RUT)
        {
            try
            {
                DataBase bd = new DataBase();
                DataSet ds = new DataSet();
                bd.dadap.SelectCommand = new SqlCommand("SELECT COUNT(id) FROM proveedor WHERE rut = @rut", bd.Conn);
                bd.dadap.SelectCommand.Parameters.Add(new SqlParameter("@rut", SqlDbType.VarChar)).Value = RUT.Replace(".", "");
                bd.dadap.Fill(ds, "ExisteRut");
                if (Convert.ToInt32(ds.Tables["ExisteRut"].Rows[0].ItemArray[0]) == 1) return true;
                return false;
            }
            catch (Exception)
            {
                Utilidades.mensaje(TipoMensaje.ERROR, "No se pudo obtener el dato.", 6);
                return false;
            }
        }

        public static bool CrearProovedor(string rut, string nombre, string giro, string vendedor, string telefono, string direccion1, string direccion2, string email, int ciudad, string nota)
        {
            try
            {
                DataBase db = DataBase.GetInstance();
                if (db.conectar())
                {
                    db.dadap.InsertCommand = new SqlCommand("INSERT INTO proveedor VALUES(@rut,@nombre,@giro,@contacto,@telefono,@dir1,@dir2,@email,@ciudad,@feccreado,@nota,DEFAULT)", db.Conn);
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@rut", SqlDbType.VarChar)).Value = rut;
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@nombre", SqlDbType.VarChar)).Value = nombre;
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@giro", SqlDbType.VarChar)).Value = giro;
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@contacto", SqlDbType.VarChar)).Value = vendedor;
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@telefono", SqlDbType.VarChar)).Value = telefono;
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@dir1", SqlDbType.VarChar)).Value = direccion1;
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@dir2", SqlDbType.VarChar)).Value = direccion2;
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar)).Value = email;
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@ciudad", SqlDbType.Int)).Value = ciudad;
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@feccreado", SqlDbType.VarChar)).Value = DateTime.Now.ToString();
                    db.dadap.InsertCommand.Parameters.Add(new SqlParameter("@nota", SqlDbType.VarChar)).Value = nota;
                    db.dadap.InsertCommand.ExecuteNonQuery();
                    db.desconectar();
                    return true;
                }
                else
                {
                    Utilidades.mensaje(TipoMensaje.ERROR, "No se pudo establecer una conexión a la base de datos.", 11);
                    return false;
                }
            }
            catch (Exception ex)
            {
                Utilidades.mensaje(TipoMensaje.ERROR, ex.Message, 9);
                return false;
            }
        }
        
        #endregion

        
    }
}
